﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using QL_DTTN.DAL;
namespace QL_DTTN.DTO.DTO
{
    public partial  class LichThi
    {
        public bool Them(List<LichThi> lstLichThi)
        {
            List<string> lstStr = new List<string>();
            SqlDataProvider dp = new SqlDataProvider();
            for (int i = 0; i < lstLichThi.Count(); i++)
            {
                string subStr = " insert into " + _tableName + " (SoBaoDanh,MaPhongThi,MaMonThi) values (@pSoBaoDanh" + i + ",@pMaPhongThi" + i + ",@pMaMonThi" + i + ") ";
                dp.AddParameter(new SqlParameter("@pSoBaoDanh" + i, lstLichThi[i].SoBaoDanh));
                dp.AddParameter(new SqlParameter("@pMaPhongThi" + i, lstLichThi[i].MaPhongThi));
                dp.AddParameter(new SqlParameter("@pMaMonThi" + i, lstLichThi[i].MaMonThi));
                lstStr.Add(subStr);
            }
            return dp.runSQLListQuery(lstStr,true);
        }

        public bool KiemTraTonTai(LichThi lichThi)
        {
            string sql = " select * from " + _tableName + "  where SoBaoDanh=@pSoBaoDanh and MaMonThi=@pMaMonThi ";
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pSoBaoDanh", lichThi.SoBaoDanh));
            dp.AddParameter(new SqlParameter("@pMaMonThi", lichThi.MaMonThi));
            System.Data.SqlClient.SqlDataReader sqlRr = dp.ExDataReader(sql, true);
            if (sqlRr != null)
            {
                while (sqlRr.Read())
                {
                    return true;
                }
                dp.CloseConnection();
                return false;
            }
            else
            {
                return true;
            }
        }
        public DataTable dataTable(short maPhongThi,long maMonThi)
        {
            string sql = " select C.SoBaoDanh,C.MaPhongThi,C.MaMonThi from " + _tableName + " C  where C.MaPhongThi=@pMaPhongThi and C.MaMonThi=@pMaMonThi ";
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pMaMonThi", maMonThi));
            dp.AddParameter(new SqlParameter("@pMaPhongThi", maPhongThi));
            return dp.ExDataTable(sql, true);
        }
        public int SoThiSinh(short maPhongThi, long maMonThi)
        {
            string sql = " select Count(C.SoBaoDanh) from " + _tableName + " C  where C.MaPhongThi=@pMaPhongThi and C.MaMonThi=@pMaMonThi ";
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pMaMonThi", maMonThi));
            dp.AddParameter(new SqlParameter("@pMaPhongThi", maPhongThi));
            object kq = 0;
            dp.RunReturnSQL(sql, ref kq, true);
            return Convert.ToInt32(kq);
        }
        public DataTable getData(string key, short maTinh, string maTruong, short maHeDaoTao, long maKyThi, long maMonThi)
        {
            string sql = "select T.SoBaoDanh,T.HoTen,(Case when GioiTinh='True' then 'Nam'  else N'Nữ' end) as GioiTinh,NgaySinh,T.DiaChi,T.MaTinh,T.SoCMND,M2.TenMon,P.MaDiemThi,P.TenPhongThi ";
            sql += " from ThiSinh T inner join Truong on Truong.MaTruong=T.MaTruong ";
            sql += " left join ChiTietMonThi C on T.MaHeDaoTao=C.MaHeDaoTao ";
            sql += " left join LichThi L on T.SoBaoDanh=L.SoBaoDanh and C.MaMonThi=L.MaMonThi ";
            sql += " left join MonThi M on M.MaMonThi=C.MaMonThi ";
            sql += " left join Mon M2 on M.MaMon=M2.MaMon ";
            sql += " left join PhongThi P on P.MaPhongThi=L.MaPhongThi ";
            sql += " where (HoTen Like @pKey or T.SoBaoDanh Like @pKey) ";
            sql += ((maTruong == "0") ? ((maTinh == 0) ? "" : " and Truong.MaTinh=@pMaTinh ") : " and T.MaTruong = @pMaTruong ");
            sql += ((maHeDaoTao == 0) ? "" : " and T.MaHeDaoTao = @pMaHeDaoTao ");
            sql += ((maKyThi == 0) ? "" : " and T.MaKyThi = @pMaKyThi ");
            sql += ((maMonThi == 0) ? "" : " and C.MaMonThi = @pMaMonThi ");
            SqlDataProvider dp = new SqlDataProvider();
            dp.AddParameter(new SqlParameter("@pKey", "%" + key + "%"));
            dp.AddParameter(new SqlParameter("@pMaTruong", maTruong));
            dp.AddParameter(new SqlParameter("@pMaTinh", maTinh));
            dp.AddParameter(new SqlParameter("@pMaHeDaoTao", maHeDaoTao));
            dp.AddParameter(new SqlParameter("@pMaKyThi", maKyThi));
            dp.AddParameter(new SqlParameter("@pMaMonThi", maMonThi));
            return dp.ExDataTable(sql, true);
        }
    }
}
